package com.gack.business.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.gack.business.model.VideoStores;


public interface VideoStoresRepository extends JpaRepository<VideoStores, String>, JpaSpecificationExecutor<VideoStores>{
	public Page<VideoStores> findByStoreNameLikeAndIsUse(String storeName,Integer isUse,Pageable pageable);
	public Page<VideoStores> findByStoreAreaAndIsUse(Integer area,Integer isUse,Pageable pageable);
	public Page<VideoStores> findByStorePriceAndIsUse(Integer price,Integer isUse,Pageable pageable);
	public Page<VideoStores> findByIsUse(Integer isUse,Pageable pageable);
	public Page<VideoStores> findByStoreNameLikeAndIsUseNot(String storeName,Integer isUse,Pageable pageable);
	public Page<VideoStores> findByStoreAreaAndIsUseNot(Integer area,Integer isUse,Pageable pageable);
	public Page<VideoStores> findByStorePriceAndIsUseNot(Integer price,Integer isUse,Pageable pageable);
	public Page<VideoStores> findByIsUseNot(Integer isUse,Pageable pageable);
	public Page<VideoStores> findById(String id,Pageable pageable);
	@Query("select MAX(s.storeDeposit) as deposit from VideoStores s where s.isUse != 1")
	public Integer findMaxDeposit();
	
	@Query("select COUNT(s.id) from VideoStores s where s.storeName = :storeName and s.isUse != 1")
	public Integer getTheSameCountOfStoreName(@Param("storeName")String storeName);
	
	public List<VideoStores> findByIsUse(@Param("isUse") Integer isUse);
	// 查询被使用的办公地点总数
	public Long countByIsUse(@Param("isUse") Integer isUse);
	
	
	public List<VideoStores> findAllByIsUseNot(Integer is_use);
	
	@Query(nativeQuery = true,
			value="SELECT * from stores where id not in (select s.id from stores s left join backiuser b on s.id = b.storeid where b.pids = 2)"
					+ " and is_use != 1")
	public List<VideoStores> findAllNoUserStores();
	
	@Transactional
	@Modifying
	@Query(nativeQuery = true,
			value="update stores set store_user = null ,store_phone = null where id not in ("
					+ "select s1.* from (select s.id from stores s left join backiuser b on s.id = b.storeid where b.pids = 2) s1)")
	public int updateStoreNameAndPhone();
	
}
